const mysql = require('mysql2/promise');
const dataBase = require('../config/index.js');

// 创建连接池
const pool = mysql.createPool({
  host: dataBase.HOST,
  user: dataBase.USERNAME,
  password: dataBase.PASSWORD,
  database: dataBase.DATABASE,
  port: dataBase.PORT
})

// 封装mysql的连接
const allServices = {
  async query(sql, values) {
    try {
      const conn = await pool.getConnection();   // 等待连接池连接
      const [rows, fields] = await conn.query(sql, values);   // 执行sql语句
      pool.releaseConnection(conn);
      return Promise.resolve(rows)
    } catch (error) {
      return Promise.reject(error)
    }
  }
}

// 获取全部菜谱
const getAllCooks = (id) => {
  let _sql = `select * from cooks where userId = '${id}'`
  return allServices.query(_sql)
}

// 登录存在
const userLogin = (username, password) => {
  let _sql = `select * from users where username='${username}' and password='${password}';`
  return allServices.query(_sql)
}

// 注册
const userRegister = async (username, password) => {
  // 检查用户名是否存在的SQL查询
  let checkSql = `SELECT COUNT(*) AS count FROM users WHERE username = ?`;

  // 执行查询
  let result = await allServices.query(checkSql, [username]);

  // 检查查询结果
  if (result[0].count > 0) {
    // 用户名已存在
    return {
      note: 1,
      message: '该用户名已存在'
    }
  } else {
    // 用户名不存在，执行插入操作
    let insertSql = `INSERT INTO users (username, password) VALUES (?, ?)`;
    return await allServices.query(insertSql, [username, password]);
  }
};

// 根据用户id添加改道菜
const addCook = async (userId, data) => {
  // 构建SQL查询语句，用于检查是否存在具有相同 userId 和 steps.name 的记录
  const checkQuery = `
    SELECT * FROM cooks 
    WHERE userId = ? AND JSON_CONTAINS(steps, JSON_OBJECT('name', ?)) > 0;
  `;

  // 尝试查找具有相同 userId 和 steps.name 的记录
  const existingRecords = await allServices.query({
    sql: checkQuery,
    values: [userId, data.steps.find(step => step.name).name] // 假设 steps 是一个数组，且每个元素都有一个 'name' 属性
  });

  if (existingRecords.length > 0) {
    // 如果找到匹配的记录，返回已存在该菜名的信息
    return { note: 1, message: "已添加该食谱", cook: existingRecords[0] };
  } else {
    // 如果没有找到匹配的记录，按原计划插入新数据

    // 不需要对整个 data 进行序列化，只需要序列化特定字段
    const params = [
      userId,
      data.name,           // 直接从 data 对象中获取值
      data.time,
      data.image,
      JSON.stringify(data.material), // 序列化 material 字段
      JSON.stringify(data.flavor),   // 序列化 flavor 字段
      JSON.stringify(data.steps),    // 序列化 steps 字段
      data.difficulty,
      data.description,
      JSON.stringify(data.tips)      // 序列化 tips 字段
    ];

    // 构建SQL插入语句
    const insertQuery = `
      INSERT INTO cooks 
      (userId, name, time, image, material, flavor, steps, difficulty, description, tips) 
      VALUES 
      (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `;

    // 执行插入操作
    await allServices.query({
      sql: insertQuery,
      values: params
    });

    return { note: 2, message: "添加成功" };
  }
};

// 根据id获取单个菜
const getCookById = (id, username) => {
  let _sql = `SELECT c.*
        FROM cooks AS c
        JOIN users AS u ON c.userId = u.userId
        WHERE u.username = '${username}' AND c.id = '${id}';`
  return allServices.query(_sql)
}

// 根据id删除某个菜
const deleteCook = async (id) => {
  let _sql = `DELETE FROM cooks WHERE id = '${id}';`
  return allServices.query(_sql)
}

// 往common表中添加一条数据
const publishCook = async (data) => {
  // 将 materials 和 steps 转换为 JSON 字符串
  const materialsJson = JSON.stringify(data.materials) || null;
  const stepsJson = JSON.stringify(data.steps) || null;

  // 构造参数化查询的 SQL 语句
  let _sql = `INSERT INTO common (publishTime, name, image, authorId, materials, steps, tips, likeNum,time,status)
  VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`;

  // 参数数组
  let params = [
    data.publishTime || null,
    data.name,
    data.image,
    data.authorId || null,
    materialsJson,
    stepsJson,
    data.tips || null,
    data.likeNum !== undefined ? data.likeNum : null,
    data.time || null,
    data.status !== undefined ? data.status : null,
  ];

  // 执行查询并返回结果
  return allServices.query(_sql, params);
};

// 获取社区全部食谱
const getCommonCook = async () => {
  let _sql = `
    SELECT common.*, users.avatar, users.nickname
    FROM common
    JOIN users ON common.authorId = users.userId
    WHERE common.status = ?
  `;
  return await allServices.query(_sql, [1]);
};

// 获取社区某一道食谱
const getCommonById = async (id) => {
  let _sql = `
    SELECT c.*, u.avatar, u.nickName 
    FROM common AS c
    JOIN users AS u ON c.authorId = u.userId 
    WHERE c.id = ?;
  `;
  // 使用参数化查询避免SQL注入风险
  return allServices.query(_sql, [id]);
}

// 获取菜谱评论
const getCommentById = async (commonId) => {
  // 使用 ? 占位符来防止SQL注入，并通过数组传递参数
  let _sql = `
    SELECT c.*, u.avatar, u.nickName 
    FROM comment AS c
    JOIN users AS u ON c.userId = u.userId
    WHERE c.commonId = ?
    ORDER BY c.id DESC;
  `;

  return await allServices.query(_sql, [commonId]);
};

// 发表一条评论
const postComment = async (data) => {
  const { create_time, commonId, userId, content } = data;
  let _sql = `
    INSERT INTO comment (create_time, commonId, userId, content)
    VALUES (?, ?, ?, ?);
  `;
  return await allServices.query(_sql, [create_time, commonId, userId, content]);
};

// 收藏一个菜谱
const addMyLike = async (params) => {
  const { cookId, userId } = params;
  let _sql = `
  INSERT INTO likes (cookId, userId)
  SELECT ?, ?
  WHERE NOT EXISTS (
      SELECT 1 FROM likes
      WHERE cookId = ? AND userId = ?
  );
`;
  return await allServices.query(_sql, [cookId, userId, cookId, userId])
}

// common 表中 likeNum 自增
const addCommonLike = async (cookId) => {
  const sql = `
    UPDATE common
    SET likeNum = likeNum + 1
    WHERE id = ?;
  `;
  return await allServices.query(sql, [cookId]);
}

// 判断是否收藏过
const isLike = async (params) => {
  const { cookId, userId } = params;
  let _sql = `
    SELECT 1 FROM likes
    WHERE cookId = ? AND userId = ?
  `;
  return await allServices.query(_sql, [cookId, userId]);
}

// 删除收藏过的数据
const deteleLike = async (params) => {
  const { cookId, userId } = params;
  const _sql = `
    DELETE FROM likes
    WHERE cookId = ? AND userId = ?
  `;
  return await allServices.query(_sql, [cookId, userId]);
}

// common 表中 likeNum 自减
const deteleCommonLike = async (cookId) => {
  const sql = `
    UPDATE common
    SET likeNum = likeNum - 1
    WHERE id = ?;
  `;
  return await allServices.query(sql, [cookId]);
}

// 获取某个作者发布的所有食谱
const getMyPublish = async (authorId) => {
  let _sql = `
    SELECT c.*, u.avatar, u.nickname 
    FROM common AS c
    JOIN users AS u ON c.authorId = u.userId 
    WHERE c.authorId = ? AND c.status = ?;
  `;
  // 使用参数化查询避免SQL注入风险
  return await allServices.query(_sql, [authorId, 1]);
};

// 获取我收藏的菜谱
const getMyLike = async (userId) => {
  let _sql = `
    SELECT 
        c.*, 
        u.avatar, 
        u.nickname,
        lu.nickname AS likedByNickName,
        lu.avatar AS likedByAvatar
    FROM 
        likes AS l
    JOIN 
        common AS c ON l.cookId = c.id
    JOIN 
        users AS u ON c.authorId = u.userId
    JOIN 
        users AS lu ON l.userId = lu.userId
    WHERE 
        l.userId = ?;
  `;

  // 使用参数化查询避免SQL注入风险
  return await allServices.query(_sql, [userId]);
};

// 获取我的草稿箱
const getMyDraft = async (authorId) => {
  let _sql = `
  SELECT c.*, u.avatar, u.nickname 
  FROM common AS c
  JOIN users AS u ON c.authorId = u.userId 
  WHERE c.authorId = ? AND c.status = ?;
`;
  // 使用参数化查询避免SQL注入风险
  return await allServices.query(_sql, [authorId, 0]);
};

// 将草稿状态变为发布状态
const changePublish = async (id, publishTime) => {
  let _sql = `
  UPDATE common 
  SET status = ?, publishTime = ? 
  WHERE id = ?;
`;
  return await allServices.query(_sql, [1, publishTime, id])
}
// 更新草稿，根据传入的 id 更新整条记录为 param 对象的内容
const updateCook = async (id, param) => {
  // 从 param 中提取所有需要更新的字段，并提供默认值 "" （空字符串）
  const {
    image,
    name,
    time = "",
    materials = "",
    steps = "",
    tips = "",
    authorId = "",
    publishTime = "",
    likeNum = "",
    status = ""
  } = param;

  // 转换为 JSON 字符串的字段（如果存在且不为空字符串）
  const materialsJson = materials !== "" ? JSON.stringify(materials) : null;
  const stepsJson = steps !== "" ? JSON.stringify(steps) : null;

  // 构造 SQL 语句
  let _sql = `
    UPDATE common 
    SET 
      image = ?,
      name = ?,
      time = ?,
      materials = ?,
      steps = ?,
      tips = ?,
      authorId = ?,
      publishTime = ?,
      likeNum = ?,
      status = ?
    WHERE id = ?
  `;

  // 参数数组
  const params = [
    image || null,        // 如果 image 是空字符串，则设为 null
    name || null,         // 如果 name 是空字符串，则设为 null
    time || null,         // 如果 time 是空字符串，则设为 null
    materialsJson,        // 已处理过的 JSON 字符串或 null
    stepsJson,            // 已处理过的 JSON 字符串或 null
    tips || null,         // 如果 tips 是空字符串，则设为 null
    authorId || null,     // 如果 authorId 是空字符串，则设为 null
    publishTime || null,  // 如果 publishTime 是空字符串，则设为 null
    likeNum || 0,      // 如果 likeNum 是空字符串，则设为 null
    status || 0,       // 如果 status 是空字符串，则设为 null
    id                    // id 不会是空字符串，因此无需额外处理
  ];

  // 执行 SQL 更新
  return await allServices.query(_sql, params);
};


// 获取首页两个社区的菜谱
const getCookByHome = async () => {
  let _sql = `
    SELECT common.*, users.avatar, users.nickname
    FROM common
    JOIN users ON common.authorId = users.userId
    WHERE common.status = ?
    ORDER BY common.likeNum DESC
    LIMIT 2
`;
  return await allServices.query(_sql, [1]);
}

// 根据菜名获取菜谱，搜索功能
const getCookByName = async (cookName) => {
  if (!cookName || typeof cookName !== 'string') {
    return []; // 参数校验
  }

  let _sql = `
    SELECT common.*, users.avatar, users.nickname
    FROM common
    JOIN users ON common.authorId = users.userId
    WHERE common.status = ? AND common.name LIKE ?
  `;

  const params = [1, `%${cookName.trim()}%`]; // 模糊匹配
  return await allServices.query(_sql, params);
};

const deleteMyCook = async (id) => {
  let _sql = `
      DELETE FROM common 
      WHERE id = ?;
    `;
  return await allServices.query(_sql, [id])
}

// 修改用户头像
const updateAvatar = async (userId, image) => {
  let _sql = `UPDATE users SET avatar = ? WHERE userId = ?`;
  return await allServices.query(_sql, [image, userId]); // 使用参数化查询防止SQL注入
};

// 修改用户昵称
const updateNickname = async (userId, nickname) => {
  let _sql = `UPDATE users SET nickname = ? WHERE userId = ?`;
  return await allServices.query(_sql, [nickname, userId]); // 使用参数化查询防止SQL注入
}

// 获取每日推荐
const getRecommendDaily = async (start, end) => {
  let _sql = `
    SELECT *
    FROM common
    WHERE CAST(publishTime AS UNSIGNED) >= ? AND CAST(publishTime AS UNSIGNED) < ?
    ORDER BY likeNum DESC
    LIMIT 1;
  `;
  return await allServices.query(_sql, [start, end]);
}

module.exports = {
  getAllCooks,
  userLogin,
  addCook,
  getCookById,
  userRegister,
  deleteCook,
  publishCook,
  getCommonCook,
  getCommonById,
  getCommentById,
  postComment,
  addMyLike,
  addCommonLike,
  isLike,
  deteleLike,
  deteleCommonLike,
  getMyPublish,
  getMyLike,
  getMyDraft,
  changePublish,
  updateCook,
  getCookByHome,
  getCookByName,
  deleteMyCook,
  updateAvatar,
  getRecommendDaily,
  updateNickname
}